/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sbmart.smartserver.task;

import com.mchange.v2.c3p0.C3P0Registry;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.concurrent.ExecutorService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sbmart.object.*;
import sbmart.smartserver.cache.ConnectionManager;
import sbmart.smartserver.job.ClientTrsSalesJob;
import sbmart.smartserver.service.ClientProcedures;
import sbmart.smartserver.service.ServerTrsSalesService;
import sbmart.smartserver.service.ServerTrsService;
import sbmart.util.Ping;

/**
 *
 * @author albert
 *
 */
public class ClientTrsSalesTask implements Runnable {

    private List<Header> headers = new ArrayList<Header>();
    public static final Logger LOG = LoggerFactory.getLogger("ClientTrsSales");
    private ExecutorService exec;

    public ClientTrsSalesTask(List<Header> headers, ExecutorService exec) {
        this.headers = headers;
        this.exec = exec;
    }

    private Connection getClientConn(Header header) throws SQLException {
        LOG.debug("[CALL]");
        Connection conn = ConnectionManager.instance().getClientConnection(header);
        return conn;
    }

    private void closeClientConn(Header header, Connection conn) throws SQLException {
        LOG.debug("[CALL]");
        ConnectionManager.instance().closeClientConnection(header, conn);
    }

    private Connection getServerConn() throws SQLException {
        LOG.debug("[CALL]");
        Connection conn = C3P0Registry.pooledDataSourceByName("sbmart3").getConnection();
        conn.setAutoCommit(false);
        return conn;
    }

    private void rollback(Header header, Connection serverConn, Connection clientConn, SQLException ex) {
        LOG.error("<ERROR> " + header.getBranchId() + "|" + header.getBranchName() + "|" + header.getIpaddress(), ex);
        try {
            if (clientConn != null) {
                clientConn.rollback();
            }
            if (serverConn != null) {
                serverConn.rollback();
            }
            LOG.debug("[ROLLBACK] ", ex);
        } catch (SQLException ex1) {
            LOG.error("[ERROR]", ex1);
        } finally {
            return;
        }
    }

    @Override
    public void run() {
        try {
            LOG.info("<RUN> " + getClass().getSimpleName());
            Connection clientConn = null, serverConn = null;

            serverConn = getServerConn();

            for (Header header : headers) {
                boolean isContinue = false;
                boolean isSend = false;
                LOG.debug(header.getBranchId() + "|" + header.getBranchName() + "|" + header.getIpaddress());
                ClientTrsSalesJob.count = 0;
                try {
                    if (Ping.isReachable(header.getIpaddress())) {

                        clientConn = getClientConn(header);
                        if (clientConn == null || clientConn.isClosed()) {
                            LOG.debug("<PASS> InUsed:" + header.getBranchName() + "|" + header.getIpaddress());
                            isContinue = true;
                        }

                        if (!isContinue) {
                            putTrsSendSales(header, clientConn);
                            isSend = isTrsSendSales(header, clientConn);

                            if (isSend) {
                                putTrsSendSalesOther(header, clientConn);

                                if (serverConn.isClosed()) {
                                    serverConn = getServerConn();
                                }

                                if (!clientConn.isClosed() && !serverConn.isClosed()) {
                                    //getTrsSendGeraiSales(header, clientConn, serverConn);
                                    getInvItemBalance(header, clientConn, serverConn);
                                    getMstItemStok(header, clientConn, serverConn);
                                    
                                    getTrsSalesList(header, clientConn, serverConn);
                                    
                                } else {
                                    LOG.error("[ERROR] connection refuse or close");
                                }
                            }
                        }
                    } else {
                        LOG.error("[ERROR] PING " + header.getBranchName() + "|" + header.getIpaddress());
                    }
                } catch (SQLException ex) {
                    LOG.error("[ERROR]" + header.getBranchId() + ". " + header.getBranchName() + " " + header.getNickname() + " - " + header.getIpaddress(), ex);
                    try {
                        if (clientConn != null && !clientConn.isClosed() && isSend) {
                            clientConn.rollback();
                        }

                        if (serverConn != null && !serverConn.isClosed()) {
                            serverConn.rollback();
                        }
                    } catch (SQLException ex1) {
                        LOG.error("<ERROR> on rollback", ex1);
                    }
                } catch (Exception e) {
                    LOG.error("[ERROR]" + header.getBranchId() + ". " + header.getBranchName() + " " + header.getNickname() + " - " + header.getIpaddress() + " : " + e.getMessage());
                } finally {
                    try {
                        if (serverConn != null) {
                            serverConn.close();
                        }
                        closeClientConn(header, clientConn);



                    } catch (SQLException ex) {
                        LOG.debug("[ERROR] ", ex);
                    }
                }
            }
            LOG.info("<END> " + getClass().getSimpleName());
        } catch (SQLException ex) {
            LOG.error("<ERROR>{}", ex.getMessage());
        }
    }

    private void putTrsSendSales(Header header, Connection clientConn) throws SQLException {
        CallableStatement call_put_sendhistory_sales = clientConn.prepareCall(ClientProcedures.put_trs_sendhistory_sales);
        call_put_sendhistory_sales.execute();
        clientConn.commit();
    }

    private void putTrsSendSalesOther(Header header, Connection clientConn) throws SQLException {
        CallableStatement call_put_sendhistory_sales = clientConn.prepareCall(ClientProcedures.put_trs_sendhistory_sales_other);
        call_put_sendhistory_sales.execute();
        clientConn.commit();
    }

    private boolean isTrsSendSales(Header header, Connection clientConn) throws SQLException {
        boolean isExists = true;
        try {
            CallableStatement call_is_sendhistory_sales_exists = clientConn.prepareCall(ClientProcedures.is_sendhistory_sales_exists);
            call_is_sendhistory_sales_exists.registerOutParameter(1, Types.INTEGER);
            call_is_sendhistory_sales_exists.execute();
            int out = call_is_sendhistory_sales_exists.getInt(1);
            if (out == 0) {
                isExists = false;
                LOG.debug("<FALSE>");
            } else {
                isExists = true;
                LOG.debug("<TRUE>");
            }
        } catch (SQLException ex) {
            LOG.error("<ERROR>", ex);
        }
        return isExists;
    }

    private void getTrsSalesList(Header header, Connection clientConn, Connection serverConn) throws SQLException {
        try {
            CallableStatement call_get_trs_sales_set_status = clientConn.prepareCall(ClientProcedures.get_trs_sales_set_status);
            CallableStatement call_get_trs_sales_rekap_id = clientConn.prepareCall(ClientProcedures.get_trs_sales_rekap_id);

            boolean loop = true;
            do {
                call_get_trs_sales_rekap_id.registerOutParameter(1, Types.INTEGER);

                ConnectionManager.instance().isOpen(clientConn);
                call_get_trs_sales_rekap_id.execute();
                int rekap_id = call_get_trs_sales_rekap_id.getInt(1);

                if (rekap_id <= 0) {
                    loop = false;
                } else {
                    LOG.debug("[rekap_id] : " + rekap_id +" | "+header.getBranchId() + "." + header.getBranchName());

                    TrsSalesList trsList = new TrsSalesList();
                    List<TrsSalesMst> mst = getTrsSalesMstDetail(rekap_id, clientConn);
                    trsList.setSalesMst(mst);

                    List<TrsSalesItem> items = getTrsSalesItem(rekap_id, clientConn);
                    trsList.setSalesItems(items);

                    List<TrsSalesRekap> rekaps = getTrsSalesRekap(rekap_id, clientConn);
                    trsList.setSalesRekaps(rekaps);

                    if (mst.size() > 0 || items.size() > 0 || rekaps.size() > 0) {
                        ServerTrsSalesService.instance().putTrsSalesList(header, trsList, serverConn);
                        serverConn.commit();
                        call_get_trs_sales_set_status.setInt("rekap_id", rekap_id);

                        ConnectionManager.instance().isOpen(clientConn);
                        call_get_trs_sales_set_status.execute();
                        clientConn.commit();
                        LOG.debug("<COMMIT> : " + header.getBranchId() + ". " + header.getBranchName());
                    } else {
                        serverConn.commit();
                        call_get_trs_sales_set_status.setInt("rekap_id", rekap_id);

                        ConnectionManager.instance().isOpen(clientConn);
                        call_get_trs_sales_set_status.execute();
                        clientConn.commit();
                        LOG.debug("<COMMIT> NOT FALID : " + header.getBranchName());
                    }
                }
            } while (loop);

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private List<TrsSalesMst> getTrsSalesMstDetail(int rekap_id, Connection conn) throws SQLException {
        CallableStatement call_get_trs_sales_mst = conn.prepareCall(ClientProcedures.get_trs_sales_mst);
        CallableStatement call_get_trs_sales_detail = conn.prepareCall(ClientProcedures.get_trs_sales_detail);

        call_get_trs_sales_mst.setInt("rekap_id", rekap_id);

        ConnectionManager.instance().isOpen(conn);
        ResultSet rs = call_get_trs_sales_mst.executeQuery();
        List<TrsSalesMst> list = new ArrayList<TrsSalesMst>();
        int i = 0;
        while (rs.next()) {
            TrsSalesMst mst = new TrsSalesMst();
            int salesId = rs.getInt("sales_id");
            mst.setTransNum(rs.getString("trans_num"));
            Calendar cal = null;
            if (rs.getTimestamp("trans_date") != null) {
                cal = Calendar.getInstance();
                cal.setTimeInMillis(rs.getTimestamp("trans_date").getTime());
            }
            mst.setTransDate(cal);
            mst.setBranchId(rs.getShort("branch_id"));
            mst.setCardId(rs.getByte("card_id"));
            mst.setSubtotal(rs.getFloat("subtotal"));
            mst.setTotal(rs.getFloat("total"));
            mst.setCash(rs.getFloat("cash"));
            mst.setSumbangan(rs.getFloat("sumbangan"));
            mst.setVoucher(rs.getFloat("voucher"));
            mst.setRekapId(rs.getInt("rekap_id"));
            mst.setDisplayNum(rs.getString("display_num"));
            mst.setBayarKupon(rs.getFloat("bayar_kupon"));
            mst.setBayarTunai(rs.getFloat("bayar_tunai"));
            mst.setTarikTunai(rs.getFloat("tarik_tunai"));
            mst.setBayarDebit(rs.getFloat("bayar_debit"));

            List<TrsSalesDetail> details = new ArrayList<TrsSalesDetail>();
            call_get_trs_sales_detail.setInt(1, salesId);

            ConnectionManager.instance().isOpen(conn);
            ResultSet rs2 = call_get_trs_sales_detail.executeQuery();
            while (rs2.next()) {
                TrsSalesDetail detail = new TrsSalesDetail();
                detail.setItemId(rs2.getInt("item_id"));
                detail.setQuantity(rs2.getFloat("quantity"));
                detail.setPrice(rs2.getFloat("price"));
                detail.setHpp(rs2.getFloat("hpp"));
                detail.setDiscId(rs2.getInt("disc_id"));
                detail.setHadiahId(rs2.getInt("hadiah_id"));
                detail.setNilaiPromo(rs2.getFloat("nilai_promo"));
                detail.setHargaPromo(rs2.getFloat("harga_promo"));
                detail.setInq_id(rs2.getLong("inq_id"));
                details.add(detail);
            }
            mst.setDetails(details);
            list.add(mst);

        }
        if (!list.isEmpty()) {
            LOG.debug("<GET>");
        }
        return list;
    }

    private List<TrsSalesItem> getTrsSalesItem(int rekap_id, Connection conn) throws SQLException {
        CallableStatement call_get_trs_sales_item = conn.prepareCall(ClientProcedures.get_trs_sales_item);

        call_get_trs_sales_item.setInt("rekap_id", rekap_id);

        ConnectionManager.instance().isOpen(conn);
        ResultSet rs = call_get_trs_sales_item.executeQuery();
        List<TrsSalesItem> list = new ArrayList<TrsSalesItem>();
        int i = 0;
        while (rs.next()) {
            TrsSalesItem mst = new TrsSalesItem();

            mst.setBranchId(rs.getShort("branch_id"));
            Calendar cal1 = null;
            if (rs.getTimestamp("trans_date") != null) {
                cal1 = Calendar.getInstance();
                cal1.setTimeInMillis(rs.getTimestamp("trans_date").getTime());
            }
            mst.setTransDate(cal1);
            mst.setRekapId(rs.getInt("rekap_id"));
            mst.setItemId(rs.getInt("item_id"));
            mst.setQuantity(rs.getFloat("quantity"));
            mst.setPrice(rs.getFloat("price"));
            mst.setDiscount(rs.getFloat("discount"));
            mst.setDiscountMember(rs.getFloat("disc_member"));
            mst.setAmount(rs.getFloat("amount"));
            mst.setTax(rs.getFloat("tax"));
            mst.setHpp(rs.getFloat("hpp"));
            mst.setVendorId(rs.getInt("vendor_id"));

            list.add(mst);
        }

        if (!list.isEmpty()) {
            LOG.debug("<GET>");
        }
        return list;
    }

    private List<TrsSalesRekap> getTrsSalesRekap(int rekap_id, Connection conn) throws SQLException {
        CallableStatement call_get_trs_sales_rekap = conn.prepareCall(ClientProcedures.get_trs_sales_rekap);

        call_get_trs_sales_rekap.setInt("rekap_id", rekap_id);

        ConnectionManager.instance().isOpen(conn);
        ResultSet rs = call_get_trs_sales_rekap.executeQuery();
        List<TrsSalesRekap> list = new ArrayList<TrsSalesRekap>();
        while (rs.next()) {
            TrsSalesRekap mst = new TrsSalesRekap();
            mst.setRekapId(rs.getShort("rekap_id"));
            mst.setBranchId(rs.getShort("branch_id"));
            Calendar cal1 = null;
            if (rs.getTimestamp("trans_date") != null) {
                cal1 = Calendar.getInstance();
                cal1.setTimeInMillis(rs.getTimestamp("trans_date").getTime());
            }
            mst.setTransDate(cal1);

            Calendar cal2 = null;
            if (rs.getTimestamp("close_date") != null) {
                cal2 = Calendar.getInstance();
                cal2.setTimeInMillis(rs.getTimestamp("close_date").getTime());
            }
            mst.setCloseDate(cal2);

            mst.setUsersId(rs.getString("users_id"));
            mst.setInitial(rs.getFloat("initial"));
            mst.setCash(rs.getFloat("cash"));
            mst.setBank(rs.getFloat("bank"));
            mst.setRetur(rs.getFloat("retur"));
            mst.setDiscount(rs.getFloat("discount"));
            mst.setRentReceived(rs.getFloat("rent_received"));
            mst.setSaving(rs.getFloat("saving"));
            mst.setVariant(rs.getFloat("variant"));
            mst.setContribution(rs.getFloat("contribution"));
            mst.setVoucher(rs.getFloat("voucher"));
            mst.setCashReceived(rs.getFloat("cash_received"));
            mst.setTax(rs.getFloat("tax"));
            mst.setMargin(rs.getFloat("margin"));
            mst.setCustumer(rs.getInt("custumer"));
            mst.setInsertLog(rs.getString("insert_log"));
            mst.setEditLog(rs.getString("edit_log"));
            mst.setTarikTunai(rs.getFloat("tarik_tunai"));
            mst.setDebit(rs.getFloat("debit"));
            mst.setKupon(rs.getFloat("kupon"));
            mst.setSalesNet(rs.getFloat("sales_net"));
            mst.setReturNet(rs.getFloat("retur_net"));
            mst.setHpp(rs.getFloat("hpp"));
            mst.setVoucherAmount(rs.getFloat("voucher_amount"));
            mst.setClaim(rs.getFloat("claim"));
            mst.setBayarVariant(rs.getFloat("bayar_variant"));
            mst.setBayarMitra(rs.getFloat("bayar_mitra"));
            mst.setOnline(rs.getFloat("online"));
            mst.setCashIn(rs.getFloat("cash_in"));
            mst.setCashOut(rs.getFloat("cash_out"));
            list.add(mst);
        }
        if (!list.isEmpty()) {
            LOG.debug("<GET>");
        }
        return list;
    }

    private void getMstItemStok(Header header, Connection clientConn, Connection serverConn) throws SQLException {
        try {
            CallableStatement get_mst_item_stok = clientConn.prepareCall(ClientProcedures.get_mst_item_stok);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = get_mst_item_stok.executeQuery();
            List<MstItemStock> list = new ArrayList<MstItemStock>();
            while (rs.next()) {
                MstItemStock mst = new MstItemStock();
                mst.setStokId(rs.getInt("stok_id"));
                mst.setBranchId(rs.getShort("branch_id"));
                mst.setItemId(rs.getInt("item_id"));
                mst.setGudangId(rs.getShort("gudang_id"));
                mst.setStok(rs.getFloat("stok"));
                mst.setStokMin(rs.getFloat("stok_min"));
                mst.setStokMax(rs.getFloat("stok_max"));
                mst.setSelisih(rs.getFloat("selisih"));
                mst.setAvgSales(rs.getFloat("avg_sales"));
                mst.setDsi(rs.getShort("dsi"));
                mst.setLeadTime(rs.getByte("lead_time"));
                mst.setInsertLog(rs.getString("insert_log"));
                mst.setEditLog(rs.getString("edit_log"));

                list.add(mst);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ServerTrsSalesService.instance().putMstItemStok(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT> : " + header.getBranchId() + ". " + header.getBranchName());
            }
            clientConn.commit();
        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getInvItemBalance(Header header, Connection clientConn, Connection serverConn) throws SQLException {
        try {
            CallableStatement call_get_inv_item_balance = clientConn.prepareCall(ClientProcedures.get_inv_item_balance);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_inv_item_balance.executeQuery();
            List<InvItemBalance> list = new ArrayList<InvItemBalance>();
            while (rs.next()) {
                InvItemBalance inv = new InvItemBalance();
                //inv.setItemBalanceId(rs.getInt("item_balance_id"));
                Calendar cal1 = null;
                if (rs.getTimestamp("balance_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getTimestamp("balance_date").getTime());
                }
                inv.setBalanceDate(cal1);
                inv.setStorageId(rs.getShort("storage_id"));
                inv.setBranchId(rs.getShort("branch_id"));
                inv.setItemId(rs.getInt("item_id"));
                inv.setBalance(rs.getFloat("balance"));

                list.add(inv);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ServerTrsSalesService.instance().putInvItemBalance(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT> : " + header.getBranchId() + ". " + header.getBranchName());
            }
            clientConn.commit();
        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsSendGeraiSales(Header header, Connection clientConn, Connection serverConn) throws SQLException {
        try {
            CallableStatement call_get_trs_sendhistory_sales = clientConn.prepareCall(ClientProcedures.get_trs_sendhistory_sales);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_sendhistory_sales.executeQuery();
            List<TrsSendhistory> list = new ArrayList<TrsSendhistory>();
            while (rs.next()) {
                TrsSendhistory trs = new TrsSendhistory();
                trs.setBranchId(rs.getShort("branch_id"));
                trs.setTransId(rs.getInt("trans_id"));
                Calendar cal1 = null;
                if (rs.getTimestamp("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getTimestamp("trans_date").getTime());
                }
                trs.setTransDate(cal1);
                trs.setTransType(rs.getString("trans_type"));
                trs.setTransStatus(rs.getShort("trans_status"));
                list.add(trs);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                serverConn = ServerTrsService.instance().putTrsSendHistoryGerai(header, list, serverConn);
                serverConn.commit();
//                LOG.debug("<COMMIT> : " + header.getBranchId() + ". " + header.getBranchName());
            }
            clientConn.commit();
        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }
}
